import java.net.InetAddress;
import java.net.NetworkInterface;
import java.net.UnknownHostException;
import java.security.Timestamp;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Enumeration;



public class Conection {
	public static InetAddress ip;
	public Connection connection = null;
	public ResultSet resultSet = null;
	public Statement statement = null;
	Log 		log=new Log();
	DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	java.util.Date date = new java.util.Date ();
//    addr.getAddress().toString()
	Crypto crytop=new Crypto();

	String DataBasePath="jdbc:sqlite:/Users/fahadalfaisal/server.db";
	Conection(){
		
	}
	/*
	 * start () is used to setup the connection with the data base
	 */
	public void start() throws Exception{
			Class.forName("org.sqlite.JDBC");
			connection = DriverManager.getConnection(DataBasePath);

	}
	
	public String OwnPII(String subject){
		String res=null;
		try{
				crytop.Decryption();
				this.start();
				this.statement=this.connection.createStatement();
				this.resultSet=this.statement.executeQuery("select * from PIIAssignRole where Privilege='own' and SubjectName='"+subject+"'and ObjectName='"+subject+"'");
				res=this.resultSet.getString("Privilege");	
				
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					this.resultSet.close();
					this.statement.close();
					this.connection.close();
				} catch (Exception e) {
					e.printStackTrace();
					System.out.println("Error from the GetRole");
		
				}
	}
		crytop.encryption();
	return(res);
		
	}
	/* 
	 * this function is used to add the course the course table in the database 
	 * add course takes the course info, return true if added else false 
	 */
	public boolean AddCourse(String SemYear,String CourseNum,String CourseName,String Coursehourse,String CourseInst,String adminid) throws Exception
	{
		boolean res=false;
		String id=null;
		ip=InetAddress.getLocalHost();

		if (!CourseNum.isEmpty() && !CourseName.isEmpty())
		{
			
		try{
			crytop.Decryption();
			this.start();
			this.statement=this.connection.createStatement();		
			this.resultSet=this.statement.executeQuery("select id from Semesters where SemYear='"+SemYear+"' and CourseNum='"+CourseNum+"'");
			
			if (!this.resultSet.next())
				{		
					this.statement.executeUpdate("insert into Semesters (SemYear,CourseNum,CourseName,CourseHour,CourseInstr) values ('"+SemYear+"','"+CourseNum+"','"+CourseName+"','"+Coursehourse+"','"+CourseInst+"')");
					this.resultSet=this.statement.executeQuery("select id from Semesters where SemYear='"+SemYear+"' and CourseNum='"+CourseNum+"'");
					 id=this.resultSet.getString("id");
					res=true;
					
//					System.out.println(dateFormat.format(date)+"--"+addr.getHostAddress());

				} 
			}catch(Exception e)
				{
					e.printStackTrace();
					System.out.println("Error from the Addcourse");
				}
		finally {
				try {
					this.resultSet.close();
					this.statement.close();
					this.connection.close();
					} catch (Exception e) {
						e.printStackTrace();
						}
				}
		}
		crytop.encryption();
		if ( res) 
			log.CreateCoursesActiivites(SemYear, CourseNum, CourseName, Coursehourse,id , CourseInst, date.toString(), adminid,"normal", "Course added" ,ip.toString());
		
		
		return ( res );
	}
	
	/*
	 *	This function returns the privileges for an a Role 
	 */
	public String GetRole(String subject,String object)
	{
		String res=null;
		try{
		crytop.Decryption();
		this.start();
		this.statement=this.connection.createStatement();
		this.resultSet=this.statement.executeQuery("select Role from RoleAssign where ClassName='"+object+"' and SubjectName='"+subject+"'");
		res=this.resultSet.getString("Role");
		
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			this.resultSet.close();
			this.statement.close();
			this.connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Error from the GetRole");

		}
	}
		crytop.encryption();
	return(res);
	}
	
	
	public String PriviList(String role)
	{
		
		String res=null;
		try{
			crytop.Decryption();
		this.start();
		this.statement=this.connection.createStatement();
		this.resultSet=this.statement.executeQuery("select SetPrivilges from RoleInfo where RoleName='"+role+"'");
		res=this.resultSet.getString("SetPrivilges");	
		
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			this.resultSet.close();
			this.statement.close();
			this.connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("error from the Priv_hasPrivi");

		}
	}
		crytop.encryption();
	return(res);
	}

	/*
	 * this function retrieve the information from the data base
	 */
	public String getCourseNumbers()
	{
		boolean res=false;
		String CN="";
			try{
				crytop.Decryption();
			this.start();
			this.statement=this.connection.createStatement();	
			// need to change the sql statment 
			this.resultSet=this.statement.executeQuery("select CourseNum from Semesters");
			res=true;
			while (this.resultSet.next()) {	
				CN+=this.resultSet.getString("CourseNum")+";";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				this.resultSet.close();
				this.statement.close();
				this.connection.close();
			} catch (Exception e) {
				e.printStackTrace();
				}
			}
			crytop.encryption();
		return (CN);
	}
	
	//
	
	public String getCourseNumbersbysemester(String semester)
	{
		String CN="";
			try{
				crytop.Decryption();
			this.start();
			this.statement=this.connection.createStatement();	
			// need to change the sql statment 
			this.resultSet=this.statement.executeQuery("select CourseNum from Semesters where SemYear='"+semester+"'");
			while (this.resultSet.next()) {	
				CN+=this.resultSet.getString("CourseNum")+";";
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				this.resultSet.close();
				this.statement.close();
				this.connection.close();
			} catch (Exception e) {
				e.printStackTrace();
				}
			}
			crytop.encryption();
		return (CN);
	}
	
	/*
	 * this function is used to assign role to Student or Professor only
	 */
	public boolean AssignRole(String role,String course,String username,String semester,String adminid) throws Exception
	{	boolean res=false;
	String temp=null;
	ip=InetAddress.getLocalHost();

		 try{	
				//admin.Role.getSelectedItem()
			 crytop.Decryption();
				this.start();
				this.statement=this.connection.createStatement();
				//new one
				this.resultSet=this.statement.executeQuery("select AccountStatus from AccountInfo where UserName='"+username+"' and AccountStatus='true'");
				 temp=this.resultSet.getString("AccountStatus");
				this.resultSet=this.statement.executeQuery("select id from Semesters where SemYear='"+semester+"' and CourseNum='"+course+"'");
				
				 temp=this.resultSet.getString("id");
				this.statement.executeUpdate("insert into RoleAssign (ClassName,SubjectName,Role) values ('"+temp+"','"+username+"','"+role+"')");
				if( role=="Student"){
					this.statement.executeUpdate("insert into GradeInfo (CourseNum,StudentId,Grade) values ('"+temp+"','"+username+"','null')");
					this.statement.executeUpdate("insert into PIIAssignRole values ('"+username+"','"+temp+"','ViewLimitedPII')");
				}
				res=true;
				}
				 catch (Exception e) {
					e.printStackTrace();
					System.out.println("Assign Course Error From the Conection class");
					}
				finally{
					try {
					this.resultSet.close();
					this.statement.close();
					this.connection.close();
				}catch (Exception e) {
					e.printStackTrace();
					}
			}
		 crytop.encryption();
		 if( role=="Professor" || role=="Student"){ 
				log.AssignRoleActiivites(temp, username, role,date.toString() , adminid, "normal", "Role is assigned", ip.toString());
				 }
				 else {
					 log.AssignTatoCourseActiivites(temp, username, role, date.toString(), adminid, "normal", "Role is assigned", ip.toString());
				 }
		
			return(res);
	}
//	public String getmy_courses(String  id){
//		String s="";
//		boolean res=false;
//
//				s="courses";
//			try{
//				this.start();
//				this.statement=this.connection.createStatement( );
//				this.resultSet=this.statement.executeQuery("select * from RoleAssign  where SubjectName='"+id+"'");	
//					while (this.resultSet.next())
//					{
//					s+=";"+this.resultSet.getString("ClassName");
//					s+=";"+this.resultSet.getString("Role");
//					}
//					res=true;	
//				}
//			 catch (Exception e) {
//				e.printStackTrace();
//				System.out.println("getmy_courses in Con.");
//				}
//			finally{
//				try {
//				this.resultSet.close();
//				this.statement.close();
//				this.connection.close();
//			}catch (Exception e) {
//				e.printStackTrace();
//				}
//		}
//			System.out.println(s);
//		if (res)
//			return ( s );
//		else
//			return ( null );
//	}
	/*
	 * this function return the courses that a principal is assigned to 
	 * 
	 */
	public String get_my_courses(String id){
			boolean res=false;
			String s="my_courses";
		try{
			crytop.Decryption();
				this.start();
				this.statement=this.connection.createStatement();
				this.resultSet=this.statement.executeQuery("select ClassName,Role,CourseName,CourseHour from RoleAssign,Semesters where SubjectName='"+id+"' and RoleAssign.ClassName=Semesters.id ");	
				while (this.resultSet.next())
				{
				s+=";"+this.resultSet.getString("ClassName");
				s+=";"+this.resultSet.getString("Role");
				s+=";"+this.resultSet.getString("CourseName");
				res=true;
				}
					
			}
		 catch (Exception e) {
			e.printStackTrace();
			System.out.println("error in get_my_courses Con class");
			}
		finally{
			try {
			this.resultSet.close();
			this.statement.close();
			this.connection.close();
		}catch (Exception e) {
			e.printStackTrace();
			}
	}
		crytop.encryption();
	if (res)
		return ( s );
	else
		return ( s+";null" );
	}
	public String Get_PII( String s )
	{
		
		String info="my_info";
		try{
			crytop.Decryption();
				this.start();
				this.statement=this.connection.createStatement();
				this.resultSet=this.statement.executeQuery("select * from AccountInfo where UserName='"+s+"' ");	
				while ( this.resultSet.next( ) )
				{
				info+=";"+this.resultSet.getString("PersonalEmail");
				info+=";"+this.resultSet.getString("PersonalName");
				info+=";"+this.resultSet.getString("PersonalAddress");
				info+=";"+this.resultSet.getString("PersonalPhone");
				//res=true;
				}
					
			}
		 catch (Exception e) {
			e.printStackTrace();
			System.out.println("error in get_my_courses Con class");
			}
		finally{
			try {
			this.resultSet.close();
			this.statement.close();
			this.connection.close();
		}catch (Exception e) {
			e.printStackTrace();
			}
	}
		crytop.encryption();
		return ( info );
	}
	
	/*
	 *  this function is used to get the student list from a certain class for a professor or TAs
	 *
	 */
	public String[][] Get_Students(String[] line)
	{
		// line : P,acountid,Get_Students,courseid

		boolean check=false;

			String[][] StudentList=null;
			try{
				crytop.Decryption();
				this.start();
				this.statement=this.connection.createStatement();
				this.resultSet=this.statement.executeQuery("SELECT COUNT(*) as count FROM GradeInfo where CourseNum='"+line[3]+"'");
				String length=this.resultSet.getString("count");
				this.resultSet=this.statement.executeQuery("select * from GradeInfo where CourseNum='"+line[3]+"'");	
				StudentList=new String[Integer.parseInt(length)][2];
				int i=0;
				while (this.resultSet.next())
					{
					StudentList[i][0]=this.resultSet.getString("StudentId");
					StudentList[i][1]=this.resultSet.getString("Grade");
					i++;
					}
					check=true;	
				}
			 catch (Exception e) {
				e.printStackTrace();
				System.out.println("error from Get_Students in Con");
				}
			finally{
				try {
				this.resultSet.close();
				this.statement.close();
				this.connection.close();
			}catch (Exception e) {
				e.printStackTrace();
				}
			}
			crytop.encryption();
			return (StudentList);
	}
	
	public String PIIPriv(String subject,String Object){
		String res=null;
		try{
			crytop.Decryption();
				this.start();
				this.statement=this.connection.createStatement();
				this.resultSet=this.statement.executeQuery("select * from PIIAssignRole where SubjectName='"+subject+"'and ObjectName='"+Object+"'");
				res=this.resultSet.getString("Privilege");	
				
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					this.resultSet.close();
					this.statement.close();
					this.connection.close();
				} catch (Exception e) {
					e.printStackTrace();
					System.out.println("Error from the GetRole");
		
				}
	}
		crytop.encryption();
	return(res);
		
	}
	public String GetLimitedPII(String StudenUsername)
	{
		String res=";";
		try{
			crytop.Decryption();
				this.start();
				this.statement=this.connection.createStatement();
				this.resultSet=this.statement.executeQuery("select * from AccountInfo where UserName='"+StudenUsername+"' ");	
				while ( this.resultSet.next( ) )
				{
				res+=this.resultSet.getString("PersonalEmail");
				res+=";"+this.resultSet.getString("PersonalName");
				res+=";"+this.resultSet.getString("PersonalPhone");
				}
					
			}
		 catch (Exception e) {
			e.printStackTrace();
			System.out.println("error in get_my_courses Con class");
			}
		finally{
			try {
			this.resultSet.close();
			this.statement.close();
			this.connection.close();
		}catch (Exception e) {
			e.printStackTrace();
			}
	}
		crytop.encryption();
		return (res);
	}
	
	 
    public String Register(String[] line)
    {
            String res="Rigesteration ";
                    try{
                    	crytop.Decryption();
                                    this.start();
                                    this.statement=this.connection.createStatement();       
                                    this.statement.executeUpdate("insert into AccountInfo (UserName,UserPass,PersonalEmail,PersonalName,PersonalAddress,PersonalPhone,AccountStatus) values ('"+line[1]+"','"+line[2]+"','"+line[3]+"','"+line[4]+"','"+line[5]+"','"+line[6]+"','"+"false"+"')");
                                    res+="done";
                            }
                    catch(Exception e)
                    {
                            res+="fails";
                            e.printStackTrace();
                            System.out.println("Error from the CreateAcount");
                    }
                    crytop.encryption();
            return(res);
    }
	
	
	public boolean Submit_Grade(String course,String Studentusername,String Grade,String Professor,String Professorip)
	{	boolean res=false;
		 try{	
				//admin.Role.getSelectedItem()s
			 crytop.Decryption();	
			 this.start();
				this.statement=this.connection.createStatement(); //CourseNum varchar(30),StudentId varchar(30),Grade
				this.statement.executeUpdate("update GradeInfo set Grade='"+Grade+"' where CourseNum='"+course+"' and StudentId='"+Studentusername+"'");
				
				res=true;
				}
				 catch (Exception e) {
					e.printStackTrace();
					System.out.println("Assign Course Error From the Conection class");
					}
				finally{
					try {
					this.resultSet.close();
					this.statement.close();
					this.connection.close();
				}catch (Exception e) {
					e.printStackTrace();
					}
			}
		 crytop.encryption();
		 if(res)
		 	log.SubmitGradeActiivites(course, Studentusername, Grade, date.toString(), Professor, "normal", "the grade is changed", Professorip);	
		 
		 return(res);
	}
	public String[][]Get_admin_log()
	{
		String[][]res=null;
		int r=0;
		try{
			crytop.Decryption();
			this.start();
			this.statement=this.connection.createStatement();
			this.resultSet=this.statement.executeQuery("SELECT COUNT(*) as count from GradeInfo");
			String length=this.resultSet.getString("count");
			res=new String[Integer.parseInt(length)][8];
			
			this.resultSet=this.statement.executeQuery("select * from AuditAdmin");	
			
			while ( this.resultSet.next( ) )
			{
				res[r][0]=this.resultSet.getString("Category");
				res[r][1]=this.resultSet.getString("ActivityTime");
				res[r][2]=this.resultSet.getString("ObjectName");
				res[r][3]=this.resultSet.getString("SubjectName");
				res[r][4]=this.resultSet.getString("ActivityType");
				res[r][5]=this.resultSet.getString("ActivityPriorty");
				res[r][6]=this.resultSet.getString("ActivityValue");
				res[r][7]=this.resultSet.getString("ActivityDesc");
				r++;
			}
				
		}
	 catch (Exception e) {
		e.printStackTrace();
		System.out.println("error in Get_admin_log Con class");
		}
	finally{
		try {
		this.resultSet.close();
		this.statement.close();
		this.connection.close();
	}catch (Exception e) {
		e.printStackTrace();
		}
}
		crytop.encryption();
	return ( res );
	}
}
